﻿namespace SRM.DataObjects
{
    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Web;
    using System.ComponentModel;
    using System.Data;
    using System.Data.SqlClient;

    /// <summary>
    /// Summary description for SRM_EmploymentHistory
    /// </summary>
    [DataObjectAttribute(true)]
    public class SRM_EmploymentHistory
    {

        [DataObjectMethodAttribute(DataObjectMethodType.Select, true)]
        public static DataTable GetEmploymentHistory(string applicantKey)
        {
            DataTable dtResult = null;

            using (SqlConnection conn = new SqlConnection(Help.GetConnectionString("SRM")))
            {
                DataSet ds = new DataSet();
                SqlCommand cmd = conn.CreateCommand();
                SqlDataAdapter da = new SqlDataAdapter(cmd);

                cmd.CommandText = string.Format("SELECT * FROM SRM_ApplicantEmpHistory_Tbl WHERE ApplicantKey = {0};", applicantKey);
                da.Fill(ds);

                dtResult = ds.Tables[0];
            }

            return dtResult;
        } // public static DataTable GetAwards(string applicantKey)


        [DataObjectMethodAttribute(DataObjectMethodType.Insert, true)]
        public static void InsertEmploymentHistory(string applicantKey,
            string employer, string nature, string periodFrom, string periodTo, 
            string startingPosition, string startingBasicSalary, string currentPosition, string currentBasicSalary, 
            string allowances, string aws, string bonus, string jobDuties, string reasonLeaving, 
            string currentJob, string industryTypeCode, string country)
        {
            using (SqlConnection conn = new SqlConnection(Help.GetConnectionString("SRM")))
            {
                SqlCommand cmd = conn.CreateCommand();

                cmd.CommandText = "INSERT INTO SRM_ApplicantEmpHistory_Tbl (ApplicantKey,Employer,Nature,PeriodFrom,PeriodTo,StartingPosition,StartingBasicSalary,CurrentPosition,CurrentBasicSalary,Allowances,AWS,Bonus,JobDuties,ReasonLeaving,CurrentJob,IndustryTypeCode) VALUES (@ApplicantKey,@Employer,@Nature,@PeriodFrom,@PeriodTo,@StartingPosition,@StartingBasicSalary,@CurrentPosition,@CurrentBasicSalary,@Allowances,@AWS,@Bonus,@JobDuties,@ReasonLeaving,@CurrentJob,@IndustryTypeCode);";
                cmd.Parameters.AddWithValue("@ApplicantKey", Help.GetValueOrDBNull(applicantKey));
                cmd.Parameters.AddWithValue("@Employer", Help.GetValueOrDBNull(employer));
                cmd.Parameters.AddWithValue("@Nature", Help.GetValueOrDBNull(nature));
                cmd.Parameters.AddWithValue("@PeriodFrom", Help.GetValueOrDBNull(periodFrom));
                cmd.Parameters.AddWithValue("@PeriodTo", Help.GetValueOrDBNull(periodTo));
                cmd.Parameters.AddWithValue("@StartingPosition", Help.GetValueOrDBNull(startingPosition));
                cmd.Parameters.AddWithValue("@StartingBasicSalary", Help.GetValueOrDBNull(startingBasicSalary));
                cmd.Parameters.AddWithValue("@CurrentPosition", Help.GetValueOrDBNull(currentPosition));
                cmd.Parameters.AddWithValue("@CurrentBasicSalary", Help.GetValueOrDBNull(currentBasicSalary));
                cmd.Parameters.AddWithValue("@Allowances", Help.GetValueOrDBNull(allowances));
                cmd.Parameters.AddWithValue("@AWS", Help.GetValueOrDBNull(aws));
                cmd.Parameters.AddWithValue("@Bonus", Help.GetValueOrDBNull(bonus));
                cmd.Parameters.AddWithValue("@JobDuties", Help.GetValueOrDBNull(jobDuties));
                cmd.Parameters.AddWithValue("@ReasonLeaving", Help.GetValueOrDBNull(reasonLeaving));
                cmd.Parameters.AddWithValue("@CurrentJob", Help.GetValueOrDBNull(currentJob));
                cmd.Parameters.AddWithValue("@IndustryTypeCode", Help.GetValueOrDBNull(industryTypeCode));
                cmd.Parameters.AddWithValue("@Country", Help.GetValueOrDBNull(country));

                conn.Open();
                cmd.ExecuteNonQuery();
            }
        } // public static void InsertLanguageProficiency(string applicantKey, string language, string ability, string proficiency)



        [DataObjectMethodAttribute(DataObjectMethodType.Delete, true)]
        public static void DeleteEmploymentHistory(string empHistoryKey)
        {
            using (SqlConnection conn = new SqlConnection(Help.GetConnectionString("SRM")))
            {
                SqlCommand cmd = conn.CreateCommand();

                cmd.CommandText = "DELETE FROM SRM_ApplicantEmpHistory_Tbl WHERE EmpHistoryKey = @EmpHistoryKey;";
                cmd.Parameters.AddWithValue("@EmpHistoryKey", empHistoryKey);

                conn.Open();
                cmd.ExecuteNonQuery();
            }
        } // public static void InsertLanguageProficiency(string applicantKey, string language, string ability, string proficiency)


        //Description
        //AwardingBody
        //DateAwarded
        [DataObjectMethodAttribute(DataObjectMethodType.Update, true)]
        public static void UpdateEmploymentHistory(string empHistoryKey,
            string employer, string nature, string periodFrom, string periodTo,
            string startingPosition, string startingBasicSalary, string currentPosition, string currentBasicSalary,
            string allowances, string aws, string bonus, string jobDuties, string reasonLeaving,
            string currentJob, string industryTypeCode, string country)
        {
            using (SqlConnection conn = new SqlConnection(Help.GetConnectionString("SRM")))
            {
                SqlCommand cmd = conn.CreateCommand();

                cmd.CommandText = @"
UPDATE  SRM_ApplicantEmpHistory_Tbl 
SET     Employer	=	@Employer,
        Nature	=	@Nature,
        PeriodFrom	=	@PeriodFrom,
        PeriodTo	=	@PeriodTo,
        StartingPosition	=	@StartingPosition,
        StartingBasicSalary	=	@StartingBasicSalary,
        CurrentPosition	=	@CurrentPosition,
        CurrentBasicSalary	=	@CurrentBasicSalary,
        Allowances	=	@Allowances,
        AWS	=	@AWS,
        Bonus	=	@Bonus,
        JobDuties	=	@JobDuties,
        ReasonLeaving	=	@ReasonLeaving,
        CurrentJob	=	@CurrentJob,
        IndustryTypeCode	=	@IndustryTypeCode,
        Country = @Country
WHERE   EmpHistoryKey = @EmpHistoryKey;";

                cmd.Parameters.AddWithValue("@EmpHistoryKey", Help.GetValueOrDBNull(empHistoryKey));
                cmd.Parameters.AddWithValue("@Employer", Help.GetValueOrDBNull(employer));
                cmd.Parameters.AddWithValue("@Nature", Help.GetValueOrDBNull(nature));
                cmd.Parameters.AddWithValue("@PeriodFrom", Help.GetValueOrDBNull(periodFrom));
                cmd.Parameters.AddWithValue("@PeriodTo", Help.GetValueOrDBNull(periodTo));
                cmd.Parameters.AddWithValue("@StartingPosition", Help.GetValueOrDBNull(startingPosition));
                cmd.Parameters.AddWithValue("@StartingBasicSalary", Help.GetValueOrDBNull(startingBasicSalary));
                cmd.Parameters.AddWithValue("@CurrentPosition", Help.GetValueOrDBNull(currentPosition));
                cmd.Parameters.AddWithValue("@CurrentBasicSalary", Help.GetValueOrDBNull(currentBasicSalary));
                cmd.Parameters.AddWithValue("@Allowances", Help.GetValueOrDBNull(allowances));
                cmd.Parameters.AddWithValue("@AWS", Help.GetValueOrDBNull(aws));
                cmd.Parameters.AddWithValue("@Bonus", Help.GetValueOrDBNull(bonus));
                cmd.Parameters.AddWithValue("@JobDuties", Help.GetValueOrDBNull(jobDuties));
                cmd.Parameters.AddWithValue("@ReasonLeaving", Help.GetValueOrDBNull(reasonLeaving));
                cmd.Parameters.AddWithValue("@CurrentJob", Help.GetValueOrDBNull(currentJob));
                cmd.Parameters.AddWithValue("@IndustryTypeCode", Help.GetValueOrDBNull(industryTypeCode));
                cmd.Parameters.AddWithValue("@Country", Help.GetValueOrDBNull(country));
                
                conn.Open();
                cmd.ExecuteNonQuery();
            }
        } // public static void UpdateAwards(string awardsKey,

    }
}